﻿CREATE PROCEDURE [dbo].[GetBestBuySaleProperty]
@SaleType nvarchar(3)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
IF @SaleType='SR'
select a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.pictureURL as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy, 
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
 a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
where a.saleview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.SRYes = 1)
and a.SRelistStatus = 0 and a.AgentFeedback is null and a.BestBuy=1

ELSE IF @SaleType = 'SL'
select a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.pictureURL as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
where a.saleview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.SLYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null and a.BestBuy=1

ELSE IF @SaleType = 'SI'
select a.propertyid,a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.pictureURL as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
where a.saleview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.SIYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null and a.BestBuy=1

ELSE IF @SaleType = 'SC'
select a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.pictureURL as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
where a.saleview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.SCYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null and a.BestBuy=1
ELSE
select 'WRONG INPUT PARAMETER(SR/SL/SC/SI)' AS NORESULT from AW_Setting
END

